New Plugin type: Audit

• In 6.0 since March 2008
• Now in 5.5
• At certain points in the execution flow a server emits audit events
• Events are sorted into classes and subclasses
• Event parameters depend on the event class
• Plugin declares what classes of events it is interested in

Audit Events

• Currently there is only one class: general with three subclasses
  • general log
  • error
  • result
• their parameters
  • user name
  • protocol command name and a query string
  • time and row count
  • error code

Use cases

• light-weight general log with special filters
  • for example, only specific user or only CREATE/DROP TABLE
  • the output is sent to a socket or to another host
• similarly, for a slow log (listening to the "result" events)
  • for example, alert the admin if a specific query takes longer than a given threshold
• similarly, for SQL errors
  • this is not what goes into the error log
  • security audit: log all "access denied" errors and who caused them: ER_ACCESS_DENIED, ER_DBACCESS_DENIED_ERROR, ER_TABLEACCESS_DENIED_ERROR, ER_COLUMNACCESS_DENIED_ERROR, etc

Audit Plugin Example

void security_violations(MYSQL_THD thd, const struct mysql_event *event)
{
  struct tm t;
  const struct mysql_event_general *ev = event;
  switch (ev->general_error_code) {
  case ER_ACCESS_DENIED_ERROR:
  case ER_DBACCESS_DENIED_ERROR:
  /* more error codes can go here */
    localtime_r(&ev->general_time, &t);
    pthread_mutex_lock(&lock);
    fprintf(logfile, "%04d-%02d-%02d %2d:%02d:%02d [%s] ERROR %d: %s\n",
            t.tm_year + 1900, t.tm_mon + 1,
            t.tm_mday, t.tm_hour, t.tm_min, t.tm_sec,
            ev->general_user, ev->general_error_code,
            ev->general_command);
    pthread_mutex_unlock(&lock);
  }
}

Pluggable Authentication

• New feature. Only in MariaDB 5.2
• Contributed to MySQL, version unknown
• Authentication of arbitrary complexity
  • login/password, otp, cram-md5, kerberos, pam, SO_PEERCRED, ImpersonateNamedPipeClient, ident (rfc 1413), and anything else
• Account list can be stored in the MySQL, elsewhere — LDAP, http, NIS — or both.
• Authentication plugin can be specified per user or globally
• Completely backward compatible

Client side plugins

• Authentication process is a conversation
• Client side plugins are loaded on demand or manually
• Client plugins can be specific...
  • for example, "mysql_native_password"
• ...or generic
  • for example, "dialog"
• The "dialog" plugin with a customized dialog function is included into MariaDB.

Usage

GRANT ALL PRIVILEGES ON *.* TO foo@bar 
                            IDENTIFIED VIA two_questions USING 'secret';
CREATE USER serg IDENTIFIED VIA socket_peercred;

MariaDB [test]> SELECT plugin_name, plugin_type, plugin_library,
            --> plugin_description FROM INFORMATION_SCHEMA.PLUGINS
            --> WHERE plugin_type='authentication';
+-----------------------+----------------+----------------------------------+
| PLUGIN_NAME           | PLUGIN_LIBRARY | PLUGIN_DESCRIPTION               |
+-----------------------+----------------+----------------------------------+
| mysql_native_password | NULL           | Native MySQL authentication      |
| mysql_old_password    | NULL           | Old MySQL-4.0 authentication     |
| socket_peercred       | auth_socket.so | Unix Socket based authentication |
| two_questions         | dialog.so      | Dialog plugin demo 1             |
| three_attempts        | dialog.so      | Dialog plugin demo 2             |
+-----------------------+----------------+----------------------------------+

Interface details

• Very simple API
• All the details of the MySQL protocol are completely hidden
• A typical challenge/response plugin is as simple as
  • info->write_packet(challenge);
  • info->read_packet(response);
  • return check_auth(challenge, response);

Automatic plugin negotiation

• Servers sends a connection handshake packet using a default server authentication plugin
• Client replies using a default (or specified by the caller) client authentication plugin
• Server either continues authentication or restarts it using a different plugin
• Client either continues authentication or restarts it using a different plugin
• Number of round trips is kept to a minimum

S/Key

$ skey -n 5 98 janu76882
Reminder - Do not use this program while logged in via telnet or rlogin.
Enter secret password: qwertyuiop
 94: OLIN NEAR DUG LETS SITS GOLD 
 95: BLAT BONY RUIN SCAR RACE WHY 
 96: RICE DANG JIM BOON NEAR TRAG 
 97: MOS BLOT QUAD JANE HUGE FOOL 
 98: HERB JESS BLAB VERB DAWN BEN 

Authentication Plugin Example

static int skey_auth(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info)
{
  unsigned char *pkt;
  int pkt_len;
  struct skey mp;
  char buf[SKEY_MAX_CHALLENGE+3];

  buf[0] = 2; /* ordinary question */
  if (skeychallenge(&mp, info->user_name, buf+1, SKEY_MAX_CHALLENGE) < 0)
    return CR_ERROR;
  strcat(buf, ":");
  if (vio->write_packet(vio, buf, strlen(buf)))
    return CR_ERROR;
  if ((pkt_len= vio->read_packet(vio, &pkt)) < 0)
    return CR_ERROR;
  info->password_used = 1;
  return skeyverify(&mp, pkt) ? CR_ERROR : CR_OK;
}

Demo

$ mysql --user=sktest
[mariadb] otp-md5 98 janu76882: HERB JESS BLAB VERB DAWN BEN
Your MariaDB connection id is 3
Server version: 5.2.0-MariaDB-alpha-debug Source distribution
MariaDB [test]> quit
Bye

$ mysql --user=sktest
[mariadb] otp-md5 97 janu76882: MOS BLOT QUAD JANE HUGE FOOL
Your MariaDB connection id is 4
Server version: 5.2.0-MariaDB-alpha-debug Source distribution
MariaDB [test]> quit
Bye

$ mysql --user=sktest
[mariadb] otp-md5 96 janu76882: MOS BLOT QUAD JANE HUGE FOOL
ERROR 1045 (28000): Access denied for user 'sktest'@'localhost' (using password: YES)

New Plugin type: Replication

• In 5.5
• Built on the concept of observers
  • a plugin can connect to a specified set of interfaces to "observe" events
• Supported observers are:
  • transaction observer
  • binlog storage observer
  • bunlog dump observer
  • binlog relay I/O observer
• Sometimes observers can modify what they see

Use cases

• API was created to implement semi-synchronous replication as a plugin
• Only used by semi-sync plugin
• Can be also used for
  • replication event checksums
  • encryption
• We are now working on a new generic replication API
  • where everything will be possible
  • follow the discussion on <maria-developers@lists.launchpad.net>

Server Services for Plugins

• in MySQL 5.5 and MariaDB 5.2
• Problem: plugin wants to access server functionality
  • But it must be part of the Plugin API and cannot be in the plugin.h
• Solution: Services - groups of functions, versioned independently

Server Services for Plugins

• No crashes when API changes
  • When a plugin is loaded MySQL compares versions for all Services that the plugin needs. Only for Services that the plugin needs.
• Fine grained versioning
  • A Service can be as small as one function. It still has a separate version number.
• No changes in the plugin source code
  • Calling a function from a Service looks like a regular function call
• Automatic dependency tracking
  • Every plugin binary contains a list of used Services and their versions

CREATE TABLE extension

• Only in MariaDB 5.2
• Storage Engines can extend the syntax of CREATE TABLE and ALTER TABLE
• Optional field, key, table parameters:

  CREATE TABLE data (
    name VARCHAR(255) APPROVED=YES,
    cv BLOB AVG_SIZE=2048,
    UNIQUE (name) BUCKET=adaptive
  ) ENGINE=hypothetical TAGS="data,name,CV" TRAIL=ON;
  
  ALTER TABLE data COMPRESSION=lzma LEVEL=9;
  

CREATE TABLE extension

• The engine declares what parameters it wants to support and gets them parsed and stored in the .frm automatically:

  struct ha_table_option_struct
  {
    char     *tags;        // a list of tags
    ulonglong comp_level;  // compression level
    uint      compr;       // compression algoritm
    bool      is_trailed;  // is audit trail enabled ?
  };
  
  ha_create_table_option table_option_list[]=
  {
    HA_TOPTION_STRING("tags", tags),
    HA_TOPTION_BOOL("trail", is_trailed, false),
    HA_TOPTION_ENUM("compression", compr, "none,gzip,bzip2,lzma", 0),
    HA_TOPTION_ULL("level", comp_level, 4, 0, 9, 1),
    HA_TOPTION_END
  };
  

Questions